----------------------------------------------------------------
-- @name: index_range_scan
-- @author: dion cho
-- @description: test for index range scan
-----------------------------------------------------------------

-- create objects
drop table t1 purge;

create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1);

insert into t1
select level, level 
from dual
connect by level <= 10000
;

@gather t1

-------------------------------------------
-- explain plan
explain plan for
select /*+ index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@plan


---------------------------------------------
-- plan statistics
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

-- what about "count" query
select /*+ gather_plan_statistics index(t1) */ 
  count(*) 
from t1
where c1 >= 5
;

@stat

-- what about the fetch array size
set arraysize 1
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

set arraysize 2
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat


set arraysize 15
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat


set arraysize 100
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

set arraysize 1000
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

-------------------------------------------------------
-- why the big difference by the fetch array size
-- (when only index range scan is performed without need to visit table)
@capture_on

@mysid
@mon_on &v_sid

set arraysize 1
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

@mon_off

set arraysize 1000
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

@mon_off2

@mon_show2

@capture_off


-------------------------------------------------------
-- why the big difference by the fetch array size
-- (when table fetch by rowid is involved)
@capture_on

@mysid
@mon_on &v_sid

set arraysize 1
select /*+ gather_plan_statistics index(t1) */ 
  c2 
from t1
where c1 >= 5
;

@stat

@mon_off

set arraysize 1000
select /*+ gather_plan_statistics index(t1) */ 
  c2 
from t1
where c1 >= 5
;

@stat

@mon_off2

@mon_show2

@capture_off

-------------------------------------------------------------
-- when most data is deleted
set arraysize 15

delete from t1
where c1 >= 1
;

@gather t1

explain plan for
select /*+ index(t1) */ 
  c1
from t1
where c1 >= 5
;

@plan

-- why 20 logical reads???
select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat



-- let's coalesce
alter index t1_n1 coalesce;

select /*+ gather_plan_statistics index(t1) */ 
  c1 
from t1
where c1 >= 5
;

@stat

@capture_off
